package com.uniview.service.report.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.uniview.entity.vo.OrderDetailReport;
import com.uniview.entity.vo.Pager;
import com.uniview.plugins.adapter.OLDataSource;
import com.uniview.service.report.OrderDetailReportService;
@Service("orderDetailReoprtService")
public class OrderDetailReportServiceImpl implements OrderDetailReportService {

	@Autowired
	private OLDataSource olDataSource;
	
	@Override
	public Pager<OrderDetailReport> findOrderDetailReoprt(String handler, String area, String startTime, String endTime,
			int page, int limit) {
		
		StringBuilder builder = new StringBuilder();
		builder.append("select * from (select A.*,ROWNUM as id FROM (select sd.orderNo ,sd.orderFrom,sd.creator,sd.handle,sd.handlerDept area,");
		builder.append("sd.customerName,sd.endTime,sd.createTime,sd.startTime,sd.satisfaction,");
		builder.append("sd.workHours as orderHours,sd.orderFromName,sd.orderFromNo,sd.contactName,sd.customerPhone,sd.dealStatus from SD_ORDERS sd where 1=1 and sd.status <> -99 ");
		
		if(StringUtils.isNotBlank(handler)){
			builder.append(" and sd.handle like '%"+handler+"%' ");
		}
		
		if(StringUtils.isNotBlank(area)){
			builder.append(" and (sd.handlerDept like '%"+area+"%' or sd.creatordept like '%"+area+"%' )");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			builder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		builder.append(") A WHERE ROWNUM <= " + page + " * " + limit + " ) WHERE id > (" + page + " - 1) *" + limit);
		
		
		StringBuilder countBuilder = new StringBuilder();
		
		countBuilder.append("select count(*) from SD_ORDERS sd where 1=1 and sd.status <> -99 ");
		
		if(StringUtils.isNotBlank(handler)){
			countBuilder.append(" and handle like '%"+handler+"%' ");
		}
		
		if(StringUtils.isNotBlank(area)){
			countBuilder.append(" and (handlerDept like '%"+area+"%'  or sd.creatordept like '%"+area+"%' )");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			countBuilder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		List<OrderDetailReport> list = olDataSource.queryForList(OrderDetailReport.class, builder.toString());
		
		int total = olDataSource.getCount(countBuilder.toString());
		
		Pager<OrderDetailReport> pager = new Pager<OrderDetailReport>();
		pager.setPagerList(list);
		pager.setTotalRow(total);
		return pager;
	}

	@Override
	public List<OrderDetailReport> findOrderDetailReoprt(String handler, String area, String startTime,
			String endTime) {
		StringBuilder builder = new StringBuilder();
		builder.append("select sd.orderNo ,sd.orderFrom,sd.creator,sd.handle,sd.handlerDept area,");
		builder.append("sd.customerName,sd.endTime,sd.createTime,sd.startTime,sd.satisfaction,");
		builder.append("sd.workHours as orderHours,sd.orderFromName,sd.orderFromNo,sd.contactName,sd.customerPhone,sd.dealStatus from SD_ORDERS sd where 1=1 and sd.status <> -99 ");
		
		if(StringUtils.isNotBlank(handler)){
			builder.append(" and sd.handle like '%"+handler+"%' ");
		}
		
		if(StringUtils.isNotBlank(area)){
			builder.append(" and (sd.handlerDept like '%"+area+"%' or sd.creatordept like '%"+area+"%' )");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			builder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		List<OrderDetailReport> list = olDataSource.queryForList(OrderDetailReport.class, builder.toString());
		
		return list;
	}
	
	@Override
	public Pager<OrderDetailReport> findDissatisfiedOrderReoprt(int dealStatus, String searchOrderNo, String startTime, String endTime,
			int page, int limit) {
		
		StringBuilder builder = new StringBuilder();
		builder.append("select * from (select A.*,ROWNUM as id FROM (select sd.orderNo ,sd.orderFrom,sd.creator,sd.handle,sd.handlerDept area,");
		builder.append("sd.customerName,sd.endTime,sd.createTime,sd.startTime,sd.satisfaction,");
		builder.append("sd.workHours as orderHours,sd.orderFromName,sd.orderFromNo,sd.contactName,sd.customerPhone,sd.dealStatus from SD_ORDERS sd where 1=1 and sd.status = 5 and sd.satisfaction = 0 ");
		
		if(dealStatus == 0 || dealStatus == 1){
			builder.append(" and sd.dealStatus = "+dealStatus+" ");
		}
		
		if(StringUtils.isNotBlank(searchOrderNo)){
			builder.append(" and sd.orderno like '%"+searchOrderNo+"%' ");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			builder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		builder.append("order by sd.modifytime desc");
		
		builder.append(") A WHERE ROWNUM <= " + page + " * " + limit + " ) WHERE id > (" + page + " - 1) *" + limit);
		
		
		StringBuilder countBuilder = new StringBuilder();
		
		countBuilder.append("select count(*) from SD_ORDERS sd where 1=1 and sd.status = 5 and sd.satisfaction = 0 ");
		
		if(dealStatus == 0 || dealStatus == 1){
			countBuilder.append(" and sd.dealStatus = "+dealStatus+" ");
		}
		
		if(StringUtils.isNotBlank(searchOrderNo)){
			countBuilder.append(" and sd.orderno like '%"+searchOrderNo+"%' ");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			countBuilder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		List<OrderDetailReport> list = olDataSource.queryForList(OrderDetailReport.class, builder.toString());
		
		int total = olDataSource.getCount(countBuilder.toString());
		
		Pager<OrderDetailReport> pager = new Pager<OrderDetailReport>();
		pager.setPagerList(list);
		pager.setTotalRow(total);
		return pager;
	}
	
	@Override
	public List<OrderDetailReport> findDissatisfiedOrderReoprt(int dealStatus, String searchOrderNo, String startTime,
			String endTime) {
		StringBuilder builder = new StringBuilder();
		builder.append("select sd.orderNo ,sd.orderFrom,sd.creator,sd.handle,sd.handlerDept area,");
		builder.append("sd.customerName,sd.endTime,sd.createTime,sd.startTime,sd.satisfaction,");
		builder.append("sd.workHours as orderHours,sd.orderFromName,sd.orderFromNo,sd.contactName,sd.customerPhone,sd.dealStatus from SD_ORDERS sd where 1=1 and sd.status = 5 and sd.satisfaction = 0");
		
		if(dealStatus == 0 || dealStatus == 1){
			builder.append(" and sd.dealStatus = "+dealStatus+" ");
		}
		
		if(StringUtils.isNotBlank(searchOrderNo)){
			builder.append(" and sd.orderno like '%"+searchOrderNo+"%' ");
		}
		
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			builder.append(" and sd.startTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		
		List<OrderDetailReport> list = olDataSource.queryForList(OrderDetailReport.class, builder.toString());
		
		return list;
	}
	
	@Override
	public List<OrderDetailReport> findDissatisfiedOrderReoprt() {
		StringBuilder builder = new StringBuilder();
		builder.append("select sd.orderNo ,sd.orderFrom,sd.creator,sd.handle,sd.handlerDept area,");
		builder.append("sd.customerName,sd.endTime,sd.createTime,sd.startTime,sd.satisfaction,");
		builder.append("sd.workHours as orderHours,sd.orderFromName,sd.orderFromNo,sd.contactName,sd.customerPhone,sd.dealStatus from SD_ORDERS sd where 1=1 and sd.status = 5 and sd.satisfaction = 0 and sd.dealStatus = 0");
		builder.append("order by sd.modifytime desc");
		List<OrderDetailReport> list = olDataSource.queryForList(OrderDetailReport.class, builder.toString());
		
		return list;
	}

}
